package com.hulu.yec.teach.testquestion.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.hulu.yec.common.Constant;
import com.hulu.yec.common.PageModel;
import com.hulu.yec.common.exception.DaoException;
import com.hulu.yec.common.exception.DaoRuntimeException;
import com.hulu.yec.common.util.DBUtil;
import com.hulu.yec.common.util.LogUtil;
import com.hulu.yec.teach.testquestion.enetity.AddEntity;
import com.hulu.yec.teach.testquestion.enetity.ConditionEntity;
import com.hulu.yec.teach.testquestion.enetity.QuestionEntity;

/**
 * 试题分页条件查询
 * 
 * @author zm
 *
 */
public class QuestionDao {

	public PageModel<QuestionEntity> list(ConditionEntity conditionEntity,
			int pageno, int pagesize) {
		List<Object> param = new ArrayList<>();
		StringBuffer sb = new StringBuffer();
		sb.append("select q.id, ");
		sb.append("q.question, ");
		sb.append("q.question_type, ");
		sb.append("c.name AS 'courseName', ");
		sb.append("q.difficulty, ");
		sb.append("q.state from qes_qestions q,");
		sb.append("sys_course c WHERE q.course_id=c.id ");
		if (conditionEntity.getQuestion() != null) {
			sb.append(" and q.question like ? ");
			param.add("%" + conditionEntity.getQuestion() + "%");
		}
		if (conditionEntity.getQuestion_type() != 0) {
			sb.append(" and q.question_type=? ");
			param.add(conditionEntity.getQuestion_type());
		}
		if (conditionEntity.getCourseName() != null) {
			sb.append(" and c.name=? ");
			param.add(conditionEntity.getCourseName());
		}
		if (conditionEntity.getDifficulty() != 0) {
			sb.append(" and q.difficulty=? ");
			param.add(conditionEntity.getDifficulty());
		}
		if (conditionEntity.getState() != 0) {
			sb.append(" and q.state=? ");
			param.add(conditionEntity.getState());
		}
		sb.append(" ORDER BY q.id DESC");
		sb.append(" LIMIT ? , ?");
		param.add((pageno - 1) * pagesize);
		param.add(pagesize);
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sb.toString());
			for (int i = 0; i < param.size(); i++) {
				preparedStatement.setObject(i + 1, param.get(i));
			}
			resultSet = preparedStatement.executeQuery();
			List<QuestionEntity> questionList = new ArrayList<>();
			while (resultSet.next()) {
				QuestionEntity question = new QuestionEntity();
				question.setId(resultSet.getInt("id"));
				question.setQuestion(resultSet.getString("question"));
				question.setQuestion_type(resultSet.getInt("question_type"));
				question.setCourseName(resultSet.getString("courseName"));
				question.setDifficulty(resultSet.getInt("difficulty"));
				question.setState(resultSet.getInt("state"));
				questionList.add(question);
			}
			return new PageModel<QuestionEntity>(questionList, pageno,
					pagesize, getallRecords(conditionEntity));
		} catch (SQLException e) {
			throw new DaoRuntimeException("sql错误");
		}finally {
			DBUtil.close(connection, preparedStatement, resultSet);
		}
	}

	/**
	 * 获取总条数
	 * @param conditionEntity
	 * @return
	 */
	private int getallRecords(ConditionEntity conditionEntity) {
		List<Object> param = new ArrayList<Object>();
		StringBuffer sb = new StringBuffer();
		sb.append("select count(*) ");
		sb.append("from qes_qestions q,sys_course c ");
		sb.append("WHERE q.course_id=c.id");
		if (conditionEntity.getQuestion() != null) {
			sb.append(" and q.question like ? ");
			param.add("%" + conditionEntity.getQuestion() + "%");
		}
		if (conditionEntity.getQuestion_type() != 0) {
			sb.append(" and q.question_type=? ");
			param.add(conditionEntity.getQuestion_type());
		}
		if (conditionEntity.getCourseName() != null) {
			sb.append(" and c.name=? ");
			param.add(conditionEntity.getCourseName());
		}
		if (conditionEntity.getDifficulty() != 0) {
			sb.append(" and q.difficulty=? ");
			param.add(conditionEntity.getDifficulty());
		}
		if (conditionEntity.getState() != 0) {
			sb.append(" and q.state=? ");
			param.add(conditionEntity.getState());
		}
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sb.toString());
			for (int i = 0; i < param.size(); i++) {
				preparedStatement.setObject(i + 1, param.get(i));
			}
			resultSet = preparedStatement.executeQuery();
			resultSet.next();
			return resultSet.getInt("count(*)");
		}  catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException("数据库查询失败");
		} finally {
			DBUtil.close(connection, preparedStatement, resultSet);
		}

	}

	/**
	 * 查询传入ID的状态并返回
	 * 
	 * @param id
	 * @return
	 */
	public int getState(int id) {
		String sql = "SELECT state FROM qes_qestions WHERE id =" + id;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();
			resultSet.next();
			return resultSet.getInt("state");
		} catch (SQLException e) {
			throw new DaoRuntimeException("查询异常！！");
		} finally {
			DBUtil.close(connection, preparedStatement, resultSet);
		}
	}

	/**
	 * 状态更新
	 * 
	 * @param id
	 * @param reState
	 * @return
	 * @throws DaoException
	 */
	public int updateState(int id, int reState) {
		String sql = "UPDATE qes_qestions SET state =" + reState
				+ "  WHERE id=" + id;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			return preparedStatement.executeUpdate(sql);
		} catch (SQLException e) {
			throw new RuntimeException("改角色出现sql异常！！");
		} finally {
			DBUtil.close(connection, preparedStatement);
		}
	}

	/**
	 * 得到试题信息
	 * @param id
	 * @return  
	 */
	public List<QuestionEntity> getAllQuestionInfo(int id) {
		StringBuffer sb = new StringBuffer();
		sb.append("select q.id, ");
		sb.append("q.question, ");
		sb.append("q.attachment, ");
		sb.append("q.question_type, ");
		sb.append("c.name AS 'courseName', ");
		sb.append("q.answer_a, ");
		sb.append("q.answer_b, ");
		sb.append("q.answer_c, ");
		sb.append("q.answer_d, ");
		sb.append("q.answer, ");
		sb.append("q.difficulty, ");
		sb.append("q.analysis, ");
		sb.append("q.keywords, ");
		sb.append("u.name as 'userName', ");
		sb.append("q.operate_time, ");
		sb.append("q.state, ");
		sb.append("q.memo from qes_qestions q,");
		sb.append("sys_user u, ");
		sb.append("sys_course c WHERE q.course_id=c.id AND q.operate_user_id=u.id and q.id="+id);
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sb.toString());
			resultSet = preparedStatement.executeQuery();
			List<QuestionEntity> questionList = new ArrayList<>();
			while (resultSet.next()) {
				QuestionEntity questionEntity = new QuestionEntity();
				questionEntity.setId(resultSet.getInt("id"));
				questionEntity.setQuestion(resultSet.getString("question"));
				questionEntity.setAttachment(resultSet.getString("attachment"));
				questionEntity.setQuestion_type(resultSet.getInt("question_type"));
				questionEntity.setCourseName(resultSet.getString("courseName"));
				questionEntity.setAnswer_a(resultSet.getString("answer_a"));
				questionEntity.setAnswer_b(resultSet.getString("answer_b"));
				questionEntity.setAnswer_c(resultSet.getString("answer_c"));
				questionEntity.setAnswer_d(resultSet.getString("answer_d"));
				questionEntity.setAnswer(resultSet.getString("answer"));
				questionEntity.setDifficulty(resultSet.getInt("difficulty"));
				questionEntity.setAnalysis(resultSet.getString("analysis"));
				questionEntity.setKeywords(resultSet.getString("keywords"));
				questionEntity.setUserName(resultSet.getString("userName"));
				questionEntity.setOperate_time(resultSet.getString("operate_time"));
				questionEntity.setState(resultSet.getInt("state"));
				questionEntity.setMemo(resultSet.getString("memo"));
				questionList.add(questionEntity);
			}
			return questionList;
		} catch (SQLException e) {
			throw new RuntimeException("查询试题信息出现sql异常！！");
		}finally{
			DBUtil.close(connection, preparedStatement, resultSet);
		}
	}

	/**
	 * 查询全部课程名
	 * @return
	 */
	public List<ConditionEntity> getAllCoursename() {
		String sql ="SELECT name as 'courseName' FROM sys_course";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();
			List<ConditionEntity> list = new ArrayList<>();
			while (resultSet.next()) {
				ConditionEntity con = new ConditionEntity();
				con.setCourseName(resultSet.getString("courseName"));
				list.add(con);
			}
			return list;
		} catch (SQLException e) {
			throw new DaoRuntimeException("查询异常！！");
		} finally {
			DBUtil.close(connection, preparedStatement, resultSet);
		}
	}

	/**
	 * 修改试题
	 * @return
	 */
	public int updateInfo(int id, AddEntity addEntity) {
		List<Object> param = new ArrayList<>();
		String sql = "UPDATE qes_qestions SET question=?, attachment=?, question_type=?, course_id=?, answer_a=?, answer_b=?, answer_c=?, answer_d=?, answer=?, difficulty=?, analysis=?, keywords=?,operate_time=now() ,state=?, memo=? WHERE id=" + id;
		param.add(addEntity.getQuestion());
		param.add(addEntity.getAttachment());
		param.add(addEntity.getQuestion_type());
		param.add(addEntity.getCourse_id());
		if(addEntity.getQuestion_type()==3){
			param.add(null);
			param.add(null);
			param.add(null);
			param.add(null);
		}else{
			param.add(addEntity.getAnswer_a());
			param.add(addEntity.getAnswer_b());
			param.add(addEntity.getAnswer_c());
			param.add(addEntity.getAnswer_d());
		}
		param.add(addEntity.getAnswer());
		param.add(addEntity.getDifficulty());
		param.add(addEntity.getAnalysis());
		param.add(addEntity.getKeywords());
		param.add(addEntity.getState());
		param.add(addEntity.getMemo());
		
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			for (int i = 0; i < param.size(); i++) {
				preparedStatement.setObject(i + 1, param.get(i));
			}
			return preparedStatement.executeUpdate();
		} catch (SQLException e) {
			throw new DaoRuntimeException("查询异常！！");
		} finally {
			DBUtil.close(connection, preparedStatement);
		}
	}

	public int add(AddEntity addEntity) {
		List<Object> param = new ArrayList<>();
		String sql = "INSERT INTO `qes_qestions` VALUES (NULL,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,1 ,now() , 1, ?)";
		param.add(addEntity.getQuestion());
		param.add(addEntity.getAttachment());
		param.add(addEntity.getQuestion_type());
		param.add(addEntity.getCourse_id());
		if(addEntity.getQuestion_type()==3){
			param.add(null);
			param.add(null);
			param.add(null);
			param.add(null);
		}else{
			param.add(addEntity.getAnswer_a());
			param.add(addEntity.getAnswer_b());
			param.add(addEntity.getAnswer_c());
			param.add(addEntity.getAnswer_d());
		}
		param.add(addEntity.getAnswer());
		param.add(addEntity.getDifficulty());
		param.add(addEntity.getAnalysis());
		param.add(addEntity.getKeywords());
		param.add(addEntity.getMemo());
		
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			for (int i = 0; i < param.size(); i++) {
				preparedStatement.setObject(i + 1, param.get(i));
			}
			return preparedStatement.executeUpdate();
		} catch (SQLException e) {
			throw new DaoRuntimeException("查询异常！！");
		} finally {
			DBUtil.close(connection, preparedStatement);;
		}
	}

	/**
	 * 根据课程名获取其id
	 * @return
	 */
	public int getCourseID(String courseName) {
		String sql = "SELECT id FROM sys_course WHERE name='"+courseName+"'";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = DBUtil.getConnection();
			preparedStatement = connection.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();
			resultSet.next();
			return resultSet.getInt("id");
		} catch (SQLException e) {
			throw new DaoRuntimeException("查询异常！！");
		} finally {
			DBUtil.close(connection, preparedStatement, resultSet);
		}
	}

}
